package nus.iss.laps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import nus.iss.laps.cri.Criteria;
import nus.iss.laps.general.RecordStatus;
import nus.iss.laps.model.Employee;

public class EmployeeDAO extends DAO implements IEmployeeDAO, RowMapper<Employee> {

	@Autowired
	private DataSource dataSource;
	
	public EmployeeDAO()	{
		table = "employee";
	}
	
	@Override
	public boolean addEmployee(Employee employee) throws SQLException {
		query = "insert into " + table + " values(?, ?, ?, ?, ?, ?, ?, ?)";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, employee.getEmployeeID(), employee.getLoginID(), employee.getName(), employee.getStatus(), employee.getPhone(), employee.getEmail(), employee.getSupervisorID(), employee.getLeaveEntitlementID()) > 0;
	}

	@Override
	public boolean updateEmployee(Employee employee) throws SQLException {
		query = "update " + table + " set "+
				"email = ?, loginID = ?, name = ?, phone = ?, status = ?, supervisorID = ?, LeaveEntitementID = ? "+
				"where employeeID = ?";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, employee.getEmail(), employee.getLoginID(), employee.getName(), employee.getPhone(), employee.getStatus(), employee.getSupervisorID(), employee.getLeaveEntitlementID(),employee.getEmployeeID()) > 0;
	}

	@Override
	public boolean deleteEmployee(String employeeID) throws SQLException {
		query = "update " + table + " set status = " + RecordStatus.delete + " where employeeID = '" + employeeID + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}

	@Override
	public Employee getEmployee(String employeeID) throws SQLException {
		query = getSelectQuery() + " where employeeID = '" + employeeID + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		try {
			return jdbcTemplate.queryForObject(query, this);
		} catch (IncorrectResultSizeDataAccessException e) {
			return null;
		}
	}
	
	@Override
	public Employee getEmployeefromLeaveID(long leaveID) throws SQLException {
		query = getSelectQuery() + " where employeeID in (select employeeID from leaves where leaveID ='" + leaveID + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		try {
			return jdbcTemplate.queryForObject(query, this);
		} catch (IncorrectResultSizeDataAccessException e) {
			return null;
		}
	}

	@Override
	public List<Employee> getAllEmployee() throws SQLException {
		query = getSelectQuery();
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}

	@Override
	public Employee mapRow(ResultSet arg0, int arg1) throws SQLException {
		Employee l_employee = new Employee();
	    l_employee.setEmployeeID(arg0.getString("employeeID"));
	    l_employee.setName(arg0.getString("name"));
	    l_employee.setEmail(arg0.getString("email"));
	    l_employee.setLoginID(arg0.getString("loginID"));
	    l_employee.setPhone(arg0.getString("phone"));
	    l_employee.setStatus(arg0.getByte("status"));
	    l_employee.setSupervisorID(arg0.getString("supervisorID"));
	    l_employee.setLeaveEntitlementID(arg0.getInt("LeaveEntitementID"));
	      return l_employee;
	}
	
	public List<Employee> getEmployees(Criteria cri) throws SQLException{
		String l_cri = " (employeeID like '%" + cri.getKeyword() + "%'"
				+ " or name like '%" + cri.getKeyword() + "%'"
				+ " or email like '%" + cri.getKeyword() + "%'"
				+ " or loginID like '%" + cri.getKeyword() + "%'"
				+ " or phone like '%" + cri.getKeyword() + "%'"
				+ " or supervisorID like '%" + cri.getKeyword() + "%')";
		query = getSelectQuery()
				+ " where status <> " + RecordStatus.delete + " and" + l_cri 
				+ " order by employeeid desc"
				+ " limit " + cri.getRowIndex() + ", " + cri.getRowLimit();
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		
		String l_queryCount = "select count(employeeid) from " + table + " where " + l_cri;
		cri.setRecordCount(jdbcTemplate.queryForLong(l_queryCount));
		
		return jdbcTemplate.query(query, this);
	}

	@Override
	public boolean isExisted(String employeeID) throws SQLException {
		if(getEmployee(employeeID) != null)
			return true;
		return false;
	}

}
